using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Configuration.Json;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Logging.Console;
using SovaDataService.SoModels;
using SovaDataService.SovaModels;

using System;
using System.IO;

namespace SovaDataService
{
    /**
     * StackOverflow-only Database Context.
     */
    class DbContext : Microsoft.EntityFrameworkCore.DbContext
    {
        //Declaring the database sets for the models. The database sets can be queried on.
        
        // QA models
        public DbSet<Question> Questions { get; set; }
        public DbSet<Author> Authors { get; set; }
        public DbSet<Tag> Tags { get; set; }
        public DbSet<Comment> Comments { get; set; }
        public DbSet<Answer> Answers { get; set; }
        public DbQuery<WeightedWord> Words { get; set; }
        // Framework models
        public DbSet<MarkedQuestion> MarkedQuestions { get; set; }
        public DbSet<Account> Accounts { get; set; }
        public DbSet<Search> Searches { get; set; }

        /**
         * Logger factory injected with a new ConsoleLoggerProvider
         * that logs the command name at an informational level.
         */
        public static readonly LoggerFactory ConsoleLoggerFactory
        = new LoggerFactory(new[]
        {
            new ConsoleLoggerProvider((category, level)
                => category == DbLoggerCategory.Database.Command.Name
                    && level == LogLevel.Information, true)
        });

        /**
         * Overriding OnConfiguration providing our database credentials
         * from a JSON configuration file and enabled logging.
         */
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            base.OnConfiguring(optionsBuilder);

            var config = GetConfig();

            // Getting psql connection string from configuration.
            var postgresCredentials = config.GetSection("psql").Value;

            // Using Postgresql with the previously read credentials.
            optionsBuilder.UseNpgsql(postgresCredentials);

            // Enable logging to see what happens behind the scenes.
            optionsBuilder.UseLoggerFactory(ConsoleLoggerFactory)
                .EnableSensitiveDataLogging();
        }

        /**
         * Overriding OnModelCreating to provide the mapping between the model and EFC.
         */
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            // Mapping the Questions objects with their table 
            modelBuilder.Entity<Question>().ToTable("question");
            modelBuilder.Entity<Question>()
                .Property(q => q.Id)
                .HasColumnName("id");
            modelBuilder.Entity<Question>()
                .Property(q => q.Title)
                .HasColumnName("title");
            modelBuilder.Entity<Question>()
                .Property(q => q.Body)
                .HasColumnName("body");
            modelBuilder.Entity<Question>()
                .Property(q => q.CreationDate)
                .HasColumnName("creation_date")
                .HasConversion(
                    cd => DateTimeOffset.FromUnixTimeSeconds((long)cd).DateTime,
                    cd => (int)new DateTimeOffset(cd).ToUnixTimeSeconds()
                );
            modelBuilder.Entity<Question>()
                .Property(q => q.ClosedDate)
                .HasColumnName("closed_date")
                .HasConversion(
                    cd => DateTimeOffset.FromUnixTimeSeconds((long)cd).DateTime,
                    cd => (int)new DateTimeOffset(cd).ToUnixTimeSeconds()
                );
            modelBuilder.Entity<Question>()
                .Property(q => q.Score)
                .HasColumnName("score");
            modelBuilder.Entity<Question>()
                .Property(q => q.AcceptedAnswerId)
                .HasColumnName("accepted_answer_id");
            modelBuilder.Entity<Question>()
                .Property(q => q.AuthorId)
                .HasColumnName("author_id");

            // Mapping the Author objects with their table 
            modelBuilder.Entity<Author>().ToTable("author");
            modelBuilder.Entity<Author>()
                .Property(au => au.Id)
                .HasColumnName("id");
            modelBuilder.Entity<Author>()
                .Property(au => au.Name)
                .HasColumnName("display_name");
            modelBuilder.Entity<Author>()
                .Property(au => au.CreationDate)
                .HasColumnName("creation_date")
                .HasConversion(
                    cd => DateTimeOffset.FromUnixTimeSeconds((long)cd).DateTime,
                    cd => (int)new DateTimeOffset(cd).ToUnixTimeSeconds()
                );
            modelBuilder.Entity<Author>()
                .Property(au => au.Age)
                .HasColumnName("age");
            modelBuilder.Entity<Author>()
                .Property(au => au.LocationId)
                .HasColumnName("location_id");

            // Mapping the Location objects with their table 
            modelBuilder.Entity<Location>().ToTable("location");
            modelBuilder.Entity<Location>()
                .Property(l => l.Id)
                .HasColumnName("id");
            modelBuilder.Entity<Location>()
                .Property(l => l.Place)
                .HasColumnName("location");

            // Mapping the Tag objects with their table 
            modelBuilder.Entity<Tag>().ToTable("tag");
            modelBuilder.Entity<Tag>()
                .Property(l => l.Id)
                .HasColumnName("id");
            modelBuilder.Entity<Tag>()
                .Property(l => l.Name)
                .HasColumnName("tag");

            // Mapping the Answer objects with their table 
            modelBuilder.Entity<Answer>().ToTable("answer");
            modelBuilder.Entity<Answer>()
                .Property(an => an.Id)
                .HasColumnName("id");
            modelBuilder.Entity<Answer>()
                .Property(an => an.Body)
                .HasColumnName("body");
            modelBuilder.Entity<Answer>()
                .Property(an => an.Score)
                .HasColumnName("score");
            modelBuilder.Entity<Answer>()
                .Property(an => an.CreationDate)
                .HasColumnName("creation_date")
                .HasConversion(
                    cd => DateTimeOffset.FromUnixTimeSeconds((long)cd).DateTime,
                    cd => (int)new DateTimeOffset(cd).ToUnixTimeSeconds()
                );
            modelBuilder.Entity<Answer>().ToTable("answer");

            modelBuilder.Entity<Answer>()
                .Property(an => an.QuestionId)
                .HasColumnName("question_id");
            modelBuilder.Entity<Answer>()
                .Property(an => an.AuthorId)
                .HasColumnName("author_id");

            // Cardinality constraints for Answer.
            // There is no answer_id in the Question-table, meaning we should explicitly define
            // the One-to-many (q -> a) relationship here.
            // Since we already have the relation only one way and we need the other way we specify their relationship
            modelBuilder.Entity<Answer>()
                .HasOne(an => an.Question)
                .WithMany(q => q.Answers);

            // Mapping the Comment objects with their table 
            modelBuilder.Entity<Comment>().ToTable("comment");

            modelBuilder.Entity<Comment>()
                .Property(c => c.Id)
                .HasColumnName("id");

            modelBuilder.Entity<Comment>()
                .Property(c => c.Text)
                .HasColumnName("text");

            modelBuilder.Entity<Comment>()
                .Property(c => c.Score)
                .HasColumnName("score");

            modelBuilder.Entity<Comment>()
                .Property(c => c.CreationDate)
                .HasColumnName("creation_date")
                .HasConversion(
                    cd => DateTimeOffset.FromUnixTimeSeconds((long)cd).DateTime,
                    cd => (int)new DateTimeOffset(cd).ToUnixTimeSeconds());

            modelBuilder.Entity<Comment>()
                .Property(c => c.AuthorId)
                .HasColumnName("author_id");

            modelBuilder.Entity<Comment>()
                .Property(c => c.QuestionId)
                .HasColumnName("question_id");

            modelBuilder.Entity<Comment>()
                .Property(c => c.AnswerId)
                .HasColumnName("answer_id");

            // Mapping the WeightedWord objects to their DB function of origin.
            modelBuilder.Query<WeightedWord>()
                .Property(ww => ww.Frequency)
                .HasColumnName("frequency_sum");
            modelBuilder.Query<WeightedWord>()
                .Property(ww => ww.Word)
                .HasColumnName("word");

            // Mapping the MarkedQuestion objects with their table 
            modelBuilder.Entity<MarkedQuestion>().ToTable("marked_question");

            modelBuilder.Entity<MarkedQuestion>()
                .Property(mq => mq.Annotation)
                .HasColumnName("annotation");
            modelBuilder.Entity<MarkedQuestion>()
                .Property(mq => mq.AccountId)
                .HasColumnName("account_id");
            modelBuilder.Entity<MarkedQuestion>()
                .Property(mq => mq.QuestionId)
                .HasColumnName("question_id");

            modelBuilder.Entity<MarkedQuestion>()
                .HasKey(mq => new { mq.QuestionId, mq.AccountId });

            // Mapping the Account objects with their table 
            modelBuilder.Entity<Account>().ToTable("account");

            modelBuilder.Entity<Account>()
                .Property(ac => ac.Id)
                .HasColumnName("id");
            modelBuilder.Entity<Account>()
                .Property(ac => ac.Name)
                .HasColumnName("name");
            modelBuilder.Entity<Account>()
                .Property(ac => ac.Email)
                .HasColumnName("email");
            modelBuilder.Entity<Account>()
                .Property(ac => ac.Password)
                .HasColumnName("password");

            // Mapping the Search objects with their table 
            modelBuilder.Entity<Search>().ToTable("search");

            modelBuilder.Entity<Search>()
                .Property(s => s.Id)
                .HasColumnName("id");
            modelBuilder.Entity<Search>()
                .Property(s => s.SearchText)
                .HasColumnName("search_text");
            modelBuilder.Entity<Search>()
                .Property(s => s.SearchDate)
                .HasColumnName("search_date")
                .HasConversion(
                    s => DateTimeOffset.FromUnixTimeSeconds((long) s).DateTime,
                    s => (int)new DateTimeOffset(s).ToUnixTimeSeconds());
            modelBuilder.Entity<Search>()
                .Property(s => s.AccountId)
                .HasColumnName("account_id");
        }

        /**
         * Get Configuration for the entire application.
         */
        private IConfigurationRoot GetConfig()
        {
            // Read the configurations from a JSON file.
            // @see https://docs.microsoft.com/en-us/aspnet/core/fundamentals/configuration/index?view=aspnetcore-2.1#json-configuration-provider
            string projectDirectory = Directory.GetParent(Directory.GetCurrentDirectory()).Parent.Parent.FullName;

            // Dirty fix for Library + Standalone project.
            if (!projectDirectory.EndsWith("SovaDataService"))
            {
                projectDirectory = Directory.GetParent(Directory.GetCurrentDirectory()).FullName + Path.DirectorySeparatorChar + "SovaDataService";
            }

            return new ConfigurationBuilder()
                .SetBasePath(projectDirectory)
                .AddJsonFile("appsettings.development.json", optional: false, reloadOnChange: true)
                .Build();
        }
    }
}
